'''
功能：把上传到服务器的CSV分光数据剔除电流为NA和0值的，筛选需要的字段存入数据库
'''
# 导入需要的库
from datetime import datetime
import time, os, re
import openpyxl
import urllib.parse
from sortingdataprocess import *
from sqlalchemy import create_engine
import pandas as pd
import numpy as np

# 创建SQL server 数据库连接
# engine = create_engine("mssql+pymssql://sa:123456@192.168.3.236:1433/Web")

params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31;\
DATABASE=SortingDB;UID=peapp;PWD=peapp1")
# params = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.3.236;\
# DATABASE=SortingDB;UID=qatest;PWD=qatest")

paramsall = "mssql+pyodbc:///?odbc_connect=" + params

# #engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
# engine = create_engine("mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BODBC+Driver\
# +17+for+SQL+Server%7D%3BSERVER%3D192.168.3.236%3BDATABASE%3DWeb%3BUID%3Dsa%3BPWD%3D123456", fast_executemany=True)

engine = create_engine(paramsall, fast_executemany=True)
engine2 = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31:1433/QualityDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)


def deal_num(x):
    if len(x.split('/')) > 1:
        list1 = x.split('/')[1:]
        str1 = '-'.join(list1)
        year = datetime.now().year
        str3 = str(year) + '-' + str1
        datetime1 = datetime.strptime(str3, '%Y-%m-%d %H:%M')
    elif len(x.split('-')) > 1:
        list1 = x.split('-')[1:]
        str1 = '-'.join(list1)
        year = datetime.now().year
        str3 = str(year) + '-' + str1
        datetime1 = datetime.strptime(str3, '%Y-%m-%d %H:%M:%S')
    return datetime1


def round_num(num, len1, len2):
    num1 = round(float(num), len1) if num >= 0 else round(float(num), len1 - 1)
    if str(num1).startswith('0.'):
        len2 += 2
    else:
        len2 += 1
    if len(str(num1)) > len2:
        return np.nan
    else:
        return num1


# 定义格式转换方法
def csv_to_sql(csv_path, machine_id, moID, lotID, pulse, flag, is_check, double_meter, logger, conn, filename):
    # 先获取该机器、该单号的所有的测试时间，生成列表
    sql_ensure = f"SELECT DISTINCT TestTime FROM Sorting_Data WHERE MoID='{moID}' AND LotID in (SELECT " \
                 f"LotID FROM Sorting_Info WHERE MoID='{moID}')"
    df_test_time = pd.read_sql(sql_ensure, engine)
    list_test_time = list(df_test_time['TestTime'])

    # 根据machine_id确定csv文件的读取方法
    try:
        if machine_id == 0:
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                      "CCT(K)",
                                      "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
            df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                                    "测试时间": 'TestTime', \
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', \
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                                    '环境温度': 'TestTemperature'})
        elif machine_id == 1:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='utf_16', sep='\t', header=6, \
                             usecols=['NO', 'BIN号', 'VF', 'Φv', 'CIE-x', 'CIE-y', 'Ra', 'Tc', 'λd', 'λp', \
                                      "CIE-u'", "CIE-v'", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', \
                                      'R9', 'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'IF', 'Pow', 'SDCM', 'BIN名', '时间', \
                                      '光功率', '光效'])
            # 输出未处理的结果，方便代码调试
            df['光功率'] = df['光功率'] * 1000
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'NO': 'TestNO', 'BIN号': 'BinID', 'VF': 'ForwardVoltage_V', 'Φv': 'LuminousFlux_lm', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Ra': 'Ra', 'Tc': 'CCT_K', 'λd': 'DominantWavelength_nm', 'λp': 'PeakWavelength_nm', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'IF': 'Current_mA', \
                                    'Pow': 'Power_W', 'SDCM': 'SDCM', 'BIN名': 'BinName', '时间': 'TestTime',
                                    '光功率': 'RadiantFlux_mW', \
                                    '光效': 'LumiousEfficacy_lmPerW'})
        elif machine_id == 2:
            # 读取csv格式文件，按分光机002输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=0, \
                             usecols=['  NO', 'Tc(k)', 'λd(nm)', 'λp(nm)', \
                                      '△λ', 'CIE-x', 'CIE-y', 'Ra', \
                                      'фv(lm)', 'R9', 'P(mw)', 'Vf(V)', \
                                      'I(mA)', 'P(w)', 'Efficiency(lm/w)', \
                                      'SDCM', 'BIN NO.', 'BIN name', \
                                      'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', \
                                      'R10', 'R11', \
                                      'R12', 'R13', 'R14', 'R15', 'Time'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'  NO': 'TestNO', 'Tc(k)': 'CCT_K', 'λd(nm)': 'DominantWavelength_nm', \
                                    'λp(nm)': 'PeakWavelength_nm', '△λ': 'FWHM_nm', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy',
                                    'Ra': 'Ra', \
                                    'фv(lm)': 'LuminousFlux_lm', 'R9': 'R9', 'P(mw)': 'RadiantFlux_mW',
                                    'Vf(V)': 'ForwardVoltage_V', \
                                    'I(mA)': 'Current_mA', 'P(w)': 'Power_W',
                                    'Efficiency(lm/w)': 'LumiousEfficacy_lmPerW', \
                                    'SDCM': 'SDCM', 'BIN NO.': 'BinID', 'BIN name': 'BinName', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'Time': 'TestTime'})
        elif machine_id == 3:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=0, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      'IF', 'VF', 'Ф(lm)', \
                                      'η(lm/W)', 'CIE-x', 'CIE-y', \
                                      'Tc', 'Ra', 'CRI9', 'SDCM', \
                                      'P(W)', 'WL.D', 'CIE-u', 'CIE-v', \
                                      "CIE-u'", "CIE-v'", \
                                      'WL.P', 'WL.C', \
                                      'WL.H', 'Фe(mW)', 'CRI1', 'CRI2', \
                                      'CRI3', 'CRI4', 'CRI5', 'CRI6', \
                                      'CRI7', 'CRI8', 'CRI10', 'CRI11', \
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'IF': 'Current_mA', 'VF': 'ForwardVoltage_V', 'Ф(lm)': 'LuminousFlux_lm', \
                                    'η(lm/W)': 'LumiousEfficacy_lmPerW', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Tc': 'CCT_K', 'Ra': 'Ra', 'CRI9': 'R9', 'SDCM': 'SDCM', \
                                    'P(W)': 'Power_W', 'WL.D': 'DominantWavelength_nm', 'CIE-u': 'CIEu',
                                    'CIE-v': 'CIEv', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', \
                                    'WL.H': 'FWHM_nm', 'Фe(mW)': 'RadiantFlux_mW', 'CRI1': 'R1', 'CRI2': 'R2', \
                                    'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI10': 'R10', 'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
        elif machine_id == 4:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=13, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', 'C1:VF1', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                    "C1:u'": 'CIEu_1976', "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10',
                                    'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15', \
                                    'C1:VF1': 'ZenerVoltage_V', '测试时间': 'TestTime'})
        elif machine_id == 5:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=15, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF1': 'ZenerVoltage_V',
                                    'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                    "C1:u'": 'CIEu_1976', "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10',
                                    'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
        elif machine_id in (6, 7, 8, 9, 10, 11, 12, 13):
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=0,
                             usecols=['No.', 'Bin号', 'Bin代号', 'VF', 'IF', 'P(W)', 'CIE-x', 'CIE-y', 'Tc',
                                      'WL.D', 'Ф(lm)', 'Ra', 'CIE-u', 'CIE-v', "CIE-u'", "CIE-v'",
                                      'WL.P', 'WL.C', 'WL.H', 'Фe(mW)', 'η(lm/W)', 'SDCM',
                                      'CRI1', 'CRI2', 'CRI3', 'CRI4', 'CRI5', 'CRI6',
                                      'CRI7', 'CRI8', 'CRI9', 'CRI10', 'CRI11',
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])

            # 字段重命名，方便数据库写入
            df = df.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'VF': 'ForwardVoltage_V', 'IF': 'Current_mA', 'P(W)': 'Power_W', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', 'Tc': 'CCT_K', \
                                    'WL.D': 'DominantWavelength_nm', 'Ф(lm)': 'LuminousFlux_lm',
                                    'Ra': 'Ra', \
                                    'CIE-u': 'CIEu', 'CIE-v': 'CIEv', "CIE-u'": 'CIEu_1976',
                                    "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm',
                                    'WL.H': 'FWHM_nm', \
                                    'Фe(mW)': 'RadiantFlux_mW', 'η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'SDCM': 'SDCM', \
                                    'CRI1': 'R1', 'CRI2': 'R2', 'CRI3': 'R3', 'CRI4': 'R4',
                                    'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI9': 'R9', 'CRI10': 'R10',
                                    'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15',
                                    '测试时间': 'TestTime'})
        elif machine_id == 14 and not pulse:
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                      "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'Tb'])
            df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                                    "测试时间": 'TestTime', \
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                                    "峰值波长(nm)": 'PeakWavelength_nm', \
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                                    'Tb': 'TestTemperature'})
            df['Current_mA'] = df['Current_mA'] * 1000
            # df['TestTime'] = pd.to_datetime(df['TestTime'])
        elif machine_id == 14 and pulse:
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=0,
                             usecols=[" 编号", "IF(mA)", "VF(V)", "P(mW)", "光通量   Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间",
                                      "色坐标 x", "色坐标 y",
                                      "色温(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度(度)'])
            df = df.rename(
                columns={" 编号": 'TestNO', "IF(mA)": 'Current_mA', "VF(V)": 'ForwardVoltage_V', "P(mW)": 'Power_W', \
                         "光通量   Φ(lm)": 'LuminousFlux_lm', \
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                         "时间": 'TestTime', \
                         "色坐标 x": 'CIEx', "色坐标 y": 'CIEy', \
                         "色温(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                         "峰值波长(nm)": 'PeakWavelength_nm', \
                         "Ra": 'Ra', 'R1': 'R1', \
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                         'R9': 'R9', 'R10': 'R10', \
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                         '环境温度(度)': 'TestTemperature'})
            # df['Current_mA'] = df['Current_mA'] * 1000
            df['Power_W'] = round(df['Power_W'] / 1000, 4)
            df['TestTime'] = df['TestTime'].apply(lambda x: x[:-3] + ":" + x[-2:])
            # df['TestTime'] = pd.to_datetime(df['TestTime'])
        elif machine_id in (15, 23):
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                      "Tc(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度'])
            df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                                    "测试时间": 'TestTime', \
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "Tc(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                                    "峰值波长(nm)": 'PeakWavelength_nm', \
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                                    '环境温度': 'TestTemperature'})
            df['Current_mA'] = df['Current_mA'] * 1000
        elif machine_id == 16:
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间", "x", "y",
                                      "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15'])
            df = df.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "时间": 'TestTime', \
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                                    "峰值波长(nm)": 'PeakWavelength_nm', \
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df['Current_mA'] = df['Current_mA'] * 1000
        elif machine_id in (17, 18, 19):
            df = pd.read_excel(io=csv_path, sheet_name=0, header=0, \
                               usecols=["序号", "电流(mA)", "电压VF(V)", "功率(w)", "光通量(lm)", "光功率(mW)", "光效率(lm/w)", \
                                        "CIE-X", "CIE-Y", "BIN号", "色温(K)", "显色指数", "SDCM", "主波长(nm)", \
                                        "峰波长(nm)", "时间", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', 'R10', \
                                        'R11', 'R12', 'R13', 'R14', 'R15'])
            # 字段重命名，方便数据库写入
            df = df.rename(
                columns={"序号": 'TestNO', "电流(mA)": 'Current_mA', "电压VF(V)": 'ForwardVoltage_V', "功率(w)": 'Power_W', \
                         "光通量(lm)": 'LuminousFlux_lm', "光功率(mW)": 'RadiantFlux_mW',
                         "光效率(lm/w)": 'LumiousEfficacy_lmPerW', \
                         "CIE-X": 'CIEx', "CIE-Y": 'CIEy', "BIN号": 'BinName', "色温(K)": 'CCT_K', "显色指数": 'Ra', \
                         "SDCM": 'SDCM', \
                         "主波长(nm)": 'DominantWavelength_nm', "峰波长(nm)": 'PeakWavelength_nm', "时间": 'TestTime', \
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                         'R9': 'R9', 'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14',
                         'R15': 'R15'})
        elif machine_id == 22:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df = pd.read_csv(filepath_or_buffer=csv_path, encoding='gbk', header=18, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df = df.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'C1:VF1': 'ZenerVoltage_V', 'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv',
                                    "C1:u'": 'CIEu_1976', "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10',
                                    'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
        else:
            # 追加定义df,避免报错UnboundLocalError: local variable 'df' referenced before assignment
            df = pd.DataFrame()
    except Exception as e:
        logger.info('上传文件读取异常！')
        logger.error(error)
        return 1, error
    else:
        logger.info('上传文件读取成功！')

    # print(df)
    # 添加数据长度与名称二次验证
    item_code = get_item_code(moID)
    standard = get_standard_value(item_code)
    if not standard:
        match_parrent2 = f'-9-(\d+)'
        match_parrent1 = f'-9-'
    else:
        match_parrent2 = f'-{standard}-(\d+)'
        match_parrent1 = f'-{standard}-'
    result1 = re.search(match_parrent1, filename, re.M | re.I)
    if not result1 and not standard:
        return 5, None

    # 远方一号数据加CF2,CF3
    if machine_id in (0, 14, 15, 16, 23):
        # 判定冷热态
        if get_flag(moID) and abs(df['TestTemperature'].mean() - get_cold_or_hot(moID)) > 10:
            return 3, None
        logger.info(f'{machine_id}号机台，远方机台查询CF系数')
        CF2 = get_cf(get_item_code(moID), 2)
        CF3 = get_cf(get_item_code(moID), 3)
        data = df.copy()
        if machine_id == 16:
            sql_s = "SELECT TOP 1 * FROM CF2_YF ORDER BY Upload_Date desc"
            extra_data = pd.read_sql(sql_s, engine2)
            df = add_cf_data(data, CF2, CF3, logger, extra_data)
        else:
            df = add_cf_data(data, CF2, CF3, logger)

    remove_index_list = []
    remove_list = []
    # pd数据框架添加MoID和LotID列
    df_MoID = pd.DataFrame({'MoID': [moID for i in range(0, len(df.index))]})
    df_LotID = pd.DataFrame({'LotID': [lotID for i in range(0, len(df.index))]})
    df_DataStatus = pd.DataFrame({'DataStatus': [-1 for i in range(0, len(df.index))]})
    df = pd.concat([df_MoID, df_LotID, df_DataStatus, df], axis=1)

    # 剔除电流为NA和0的数据
    df = df.dropna(subset=['Current_mA'])
    df = df[df['Current_mA'] != 0]
    df = df[df['LuminousFlux_lm'] != 0]
    data_length = len(df)
    if not is_check and not double_meter and not pulse:
        if not result1:
            return 6, standard
        result2 = re.search(match_parrent2, filename, re.M | re.I)
        if not result2:
            return 8, None
        if result2 and (int(result2.group(1)) * 0.9 > data_length or data_length > int(result2.group(1)) * 1.1):
            return 7, None
    # 测试时间转成datatime格式
    if machine_id == 2:
        list_time = list(df['TestTime'])
        if len(list_time):
            print(list_time[0])
        df['TestTime'] = df['TestTime'].apply(lambda x: deal_num(x))
    else:
        df['TestTime'] = pd.to_datetime(df['TestTime'])
    # print(df)
    logger.info(f'工单:{moID},Lot:{lotID} {df.shape[0]}行 {df.shape[1]}列')
    if flag:
        logger.info(f'{machine_id}号机台，1:1数据上传')
        df['DataStatus'] = 2
    elif double_meter:
        logger.info(f'{machine_id}号机台，对测数据上传')
        df['DataStatus'] = 3
    # 输出已处理的结果与各列的数据类型，方便代码调试
    df['U01'] = datetime.now()
    # 计时开始
    t0 = time.time()
    # 数据写入数据库
    if is_check:
        s_sql = "SELECT max(ID) AS id FROM PCE_Inspection_data"
        MAX = pd.read_sql(s_sql, engine).loc[0, 'id']
        id_list = range(MAX + 1, MAX + len(df) + 1)
        # 序号为空
        df['ID'] = [i for i in id_list]
        # 机台号
        df['MachineID'] = machine_id
        # 更新时间
        df = df.rename(columns={'U01': 'UpdateTime'})
        df['DataStatus'] = 1
        df.drop(columns=['LotID'], inplace=True)
        try:
            # print("插入点检数据")
            df.to_sql('PCE_Inspection_data', engine, if_exists='append', index=None)
        except Exception as e:
            logger.error(e)
            return 2, None
        else:
            logger.info(f'{machine_id}号机台，点检数据上传成功！！')
            # 写入数据库执行计时输出
            print('机台', machine_id, '用时', round(time.time() - t0, 3), '秒')
            return 0, None
    else:
        # DataFrame格式整理
        colmuns = df.columns
        colmuns_list = []
        if 'BinID' in colmuns:
            df['BinID'] = df['BinID'].apply(lambda x: int(x))
        df['Current_mA'] = df['Current_mA'].apply(lambda x: round_num(x, 4, 15))
        colmuns_list.append('Current_mA')
        df['ForwardVoltage_V'] = df['ForwardVoltage_V'].apply(lambda x: round_num(x, 4, 15))
        colmuns_list.append('ForwardVoltage_V')
        if 'Power_W' in colmuns:
            df['Power_W'] = df['Power_W'].apply(lambda x: round_num(x, 4, 15))
            colmuns_list.append('Power_W')
        if 'LumiousEfficacy_lmPerW' in colmuns:
            df['LumiousEfficacy_lmPerW'] = df['LumiousEfficacy_lmPerW'].apply(lambda x: round_num(x, 4, 15))
            colmuns_list.append('LumiousEfficacy_lmPerW')

        if 'LuminousFlux_lm' in colmuns:
            df['LuminousFlux_lm'] = df['LuminousFlux_lm'].apply(lambda x: round_num(x, 3, 15))
            colmuns_list.append('LuminousFlux_lm')
        if 'RadiantFlux_mW' in colmuns:
            df['RadiantFlux_mW'] = df['RadiantFlux_mW'].apply(lambda x: round_num(x, 3, 18))
            colmuns_list.append('RadiantFlux_mW')

        if 'CCT_K' in colmuns:
            df['CCT_K'] = df['CCT_K'].apply(lambda x: round_num(x, 1, 15))
            colmuns_list.append('CCT_K')

        df['CIEx'] = df['CIEx'].apply(lambda x: round_num(x, 6, 6))
        colmuns_list.append('CIEx')
        df['CIEy'] = df['CIEy'].apply(lambda x: round_num(x, 6, 6))
        colmuns_list.append('CIEy')
        if 'CIEu' in colmuns:
            df['CIEu'] = df['CIEu'].apply(lambda x: round_num(x, 6, 6))
        if 'CIEv' in colmuns:
            df['CIEv'] = df['CIEv'].apply(lambda x: round_num(x, 6, 6))
        if 'CIEu_1976' in colmuns:
            df['CIEu_1976'] = df['CIEu_1976'].apply(lambda x: round_num(x, 6, 6))
        if 'CIEv_1976' in colmuns:
            df['CIEv_1976'] = df['CIEv_1976'].apply(lambda x: round_num(x, 6, 6))

        if 'SDCM' in colmuns:
            df['SDCM'] = df['SDCM'].apply(lambda x: round_num(x, 2, 15))
            colmuns_list.append('SDCM')
        df['Ra'] = df['Ra'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('Ra')
        df['R9'] = df['R9'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R9')
        if 'TestTemperature' in colmuns:
            df['TestTemperature'] = df['TestTemperature'].apply(lambda x: round_num(x, 2, 10))

        df['R1'] = df['R1'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R1')
        df['R2'] = df['R2'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R2')
        df['R3'] = df['R3'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R3')
        df['R4'] = df['R4'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R4')
        df['R5'] = df['R5'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R5')
        df['R6'] = df['R6'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R6')
        df['R7'] = df['R7'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R7')
        df['R8'] = df['R8'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R8')
        df['R10'] = df['R10'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R10')
        df['R11'] = df['R11'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R11')
        df['R12'] = df['R12'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R12')
        df['R13'] = df['R13'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R13')
        df['R14'] = df['R14'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R14')
        df['R15'] = df['R15'].apply(lambda x: round_num(x, 2, 5))
        colmuns_list.append('R15')
        if 'PeakWavelength_nm' in colmuns:
            df['PeakWavelength_nm'] = df['PeakWavelength_nm'].apply(lambda x: round_num(x, 2, 6))
            colmuns_list.append('PeakWavelength_nm')
        if 'DominantWavelength_nm' in colmuns:
            df['DominantWavelength_nm'] = df['DominantWavelength_nm'].apply(lambda x: round_num(x, 2, 6))
            colmuns_list.append('DominantWavelength_nm')
        if 'ComplementaryWavelength_nm' in colmuns:
            df['ComplementaryWavelength_nm'] = df['ComplementaryWavelength_nm'].apply(lambda x: round_num(x, 2, 6))
        if 'FWHM_nm' in colmuns:
            df['FWHM_nm'] = df['FWHM_nm'].apply(lambda x: round_num(x, 2, 6))
        if 'ZenerVoltage_V' in colmuns:
            df['ZenerVoltage_V'] = df['ZenerVoltage_V'].apply(lambda x: round_num(x, 6, 15))

        for i in df.index:
            time1 = df.loc[i, 'TestTime']
            if time1 in list_test_time:
                remove_index_list.append(i)
                remove_list.append(i + 2)
                continue
            else:
                for colmun in colmuns_list:
                    if pd.isna(df.loc[i, colmun]):
                        remove_index_list.append(i + 2)

        df = df.dropna(subset=colmuns_list)
        df = df.drop(index=remove_index_list)

        logger.info(f'{machine_id}号机台，删除空值数据行数{list(set(remove_index_list))}！！')
        logger.info(f'{machine_id}号机台，删除重复数据行数{list(set(remove_index_list))}！！')

        if df.empty:
            logger.warning(f"数据不合规范,文件：{csv_path}")
            cursor = conn.cursor()
            cursor.execute(f"DELETE FROM Sorting_Info WHERE MoID='{moID}' AND LotID='{lotID}'")
            conn.commit()
            return 4, None
        else:
            try:
                df.to_sql('Sorting_Data', engine, if_exists='append', index=None)
            except Exception as e:
                logger.error(e)
                return 2, None
            else:
                logger.info(f'{machine_id}号机台，数据上传成功！！')
                # 写入数据库执行计时输出
                print('机台', machine_id, '用时', round(time.time() - t0, 3), '秒')
                return 0, None


if __name__ == '__main__':
    csv_to_sql(csv_path=r'F:\Desktop\Work\数据文件\5104-00800120-1.csv',
               machine_id=2, moID='五号机数据', lotID=2, flag=1)
